﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace DataBaseUpdate.UpdateDateBase2013
{
    public class UpdateDateBase201310
    {
        public UpdateDateBase201310() { }
        public List<string> list = new List<string>();
        public string Message;//全局变量，装载语句执行后返回信息

        public List<string> ExecuteSQL2013100701(bool IsExcute)
        {
            list = new List<string>();

            if (IsExcute)
            {
                string SQL = @"if Not exists (select 1  from  sysobjects  where  id = object_id('CsDoorType') and   type = 'U')
create table CsDoorType (
   DoorTypeIdentifier   varchar(100)         primary key  ,
   DoorTypeName         varchar(100)         null,
   DoorTypeCode         varchar(100)         null,
   DoorTypeImgeName     varchar(100)         null,
   OrganName            varchar(250)         null,
   OrganIdentifier      varchar(100)         null
)";
                Message = SqlHelper.ExcuteQueryReturnMessage(SQL, CommandType.Text);
            }
            list.Add("创建[CsDoorType]表:" + Message);
            return list;
        }

        public List<string> ExecuteSQL2013100702(bool IsExcute)
        {
            list = new List<string>();

            if (IsExcute)
            {
                string SQL = @"if Not exists (select 1  from  sysobjects  where  id = object_id('CsDoorRoomRela')  and   type = 'U')
create table CsDoorRoomRela (
   DoorRoomRelaIdentifier varchar(100)      Primary key,
   DoorTypeIdentifier   varchar(100)         null,
   RoomTypeIdentifier   varchar(100)         null
)";
                Message = SqlHelper.ExcuteQueryReturnMessage(SQL, CommandType.Text);
            }
            list.Add("创建[CsDoorRoomRela]表:" + Message);
            return list;
        }


        public List<string> ExecuteSQL2013100703(bool IsExcute)
        {
            list = new List<string>();

            if (IsExcute)
            {
                string SQL = @"if Not exists (select 1 from  sysobjects  where  id = object_id('CsRoomType') and   type = 'U')
create table CsRoomType (
   RoomTypeIdentifier   varchar(100)         primary key,
   RoomTypeCode         varchar(100)         null,
    RoomTypeName        varchar(100)    null,
   OrganName            varchar(250)         null,
   OrganIdentifier      varchar(100)         null
)";
                Message = SqlHelper.ExcuteQueryReturnMessage(SQL, CommandType.Text);
            }
            list.Add("创建[CsRoomType]表:" + Message);
            return list;
        }

        public List<string> ExecuteSQL2013100704(bool IsExcute)
        {
            list = new List<string>();

            if (IsExcute)
            {
                string SQL = @"if Not exists (select 1  from  sysobjects  where  id = object_id('CsRoomObjectRela') and   type = 'U')
create table CsRoomObjectRela (
   RoomObjectRelaIdentifier varchar(100)       Primary key,
   RoomTypeIdentifier   varchar(100)         null,
   CsValidateObjectIdentifier varchar(100)         null
)";
                Message = SqlHelper.ExcuteQueryReturnMessage(SQL, CommandType.Text);
            }
            list.Add("创建[CsRoomObjectRela]表:" + Message);
            return list;
        }

        public List<string> ExecuteSQL2013100705(bool IsExcute)
        {
            list = new List<string>();

            if (IsExcute)
            {
                string SQL = @"if Not exists (select 1  from  sysobjects  where  id = object_id('CsValidateObject') and   type = 'U')
create table CsValidateObject (
   ValidateObjectIdentifier varchar(100)        primary key,
   ValidateObjectName   varchar(500)         null,
   ValidateObjectCode   varchar(500)         null,
   ProviderName         varchar(500)         null,
   ProviderIdentifier   varchar(500)         null,
   MaintenanceTime  int                  null,
   OrganName            varchar(250)         null,
   OrganIdentifier      varchar(100)         null
)";
                Message = SqlHelper.ExcuteQueryReturnMessage(SQL, CommandType.Text);
            }
            list.Add("创建[CsValidateObject]表:" + Message);
            return list;
        }


        public List<string> ExecuteSQL2013100706(bool IsExcute)
        {
            list = new List<string>();

            if (IsExcute)
            {
                string SQL = @"if Not exists (select 1 from  sysobjects  where  id = object_id('CsValidatePro')and   type = 'U')
create table CsValidatePro (
   ValidateProIdentifier varchar(100)         not null,
   ValidateObjectIdentifier varchar(100)         null,
   ValidateProName    varchar(500)         null,
   ValidateProCode    varchar(500)         null,
MaintenanceTime     decimal                 null,
   OrganName            varchar(500)         null,
   OrganIdentifier      varchar(100)         null,
   constraint PK_CSVALIDATEPRO primary key (ValidateProIdentifier)
)";
                Message = SqlHelper.ExcuteQueryReturnMessage(SQL, CommandType.Text);
            }
            list.Add("创建[CsValidatePro]表:" + Message);
            return list;
        }

        public List<string> ExecuteSQL2013100707(bool IsExcute)
        {
            list = new List<string>();

            if (IsExcute)
            {
                string SQL = @"if Not exists (select 1 from  sysobjects  where  id = object_id('CsValidateRoom') and   type = 'U')
create table CsValidateRoom (
   ValidateRoomIdentifier varchar(100)         not null,
   ValidateRoomCode     varchar(100)         null,
   FoundDate            datetime             null,
   ValidateStaffIdentifier varchar(100)         null,
   DeveloperOpinion     varchar(100)         null,
   SubjoinExplain       varchar(100)         null,
   ValidateRoomState    varchar(100)         null,
   UnitIdentifier       varchar(100)         null,
   OrganIdentifier      varchar(100)         null,
   constraint PK_CSVALIDATEROOM primary key (ValidateRoomIdentifier)
)";
                Message = SqlHelper.ExcuteQueryReturnMessage(SQL, CommandType.Text);
            }
            list.Add("创建[CsValidateRoom]表:" + Message);
            return list;
        }

        public List<string> ExecuteSQL2013100708(bool IsExcute)
        {
            list = new List<string>();

            if (IsExcute)
            {
                string SQL = @"if Not exists (select 1  from  sysobjects  where  id = object_id('CsValidateRoomInfo') and   type = 'U')
create table CsValidateRoomInfo (
   ValidateRInfoIdentifier varchar(100)         not null,
   ValidateRoomIdentifier varchar(100)         null,
   RoomTypeIdentifier   varchar(100)         null,
   ValidateObjectIdentifier varchar(100)         null,
   ValidateProIdentifier        varchar(100)         null,
   ValidateState        varchar(100)         null,
   ValidateExplain      varchar(100)         null,
   IsRepair             bit                 null,
   FillStaffIdentifier  varchar(100)         null,
   FillDate             datetime             null,
   RepairBeginDate      datetime             null,
   RepairEndDate        datetime             null,
   constraint PK_CSVALIDATEROOMINFO primary key (ValidateRInfoIdentifier)
)";
                Message = SqlHelper.ExcuteQueryReturnMessage(SQL, CommandType.Text);
            }
            list.Add("创建[CsValidateRoomInfo]表:" + Message);
            return list;
        }

        public List<string> ExecuteSQL2013100801(bool IsExcute)
        {
            list = new List<string>();

            if (IsExcute)
            {
                string SQL = @"if Not exists (select 1  from  sysobjects  where  id = object_id('SmOrgan')   and   type = 'U')
create table SmOrgan (
   OrganIdentifier      varchar(100)         not null,
   OrganCode            varchar(100)         null,
   OrganName            varchar(100)         null,
   constraint PK_SMORGAN primary key (OrganIdentifier)
)";
                Message = SqlHelper.ExcuteQueryReturnMessage(SQL, CommandType.Text);
            }
            list.Add("创建[SmOrgan]表:" + Message);
            return list;
        }

        public List<string> ExecuteSQL2013100802(bool IsExcute)
        {
            list = new List<string>();

            if (IsExcute)
            {
                string SQL = @"if Not exists (select 1 from  sysobjects   where  id = object_id('RmUnit') and   type = 'U')
create table RmUnit (
   UnitIdentifier       varchar(100)         not null,
   UnitName             varchar(100)         null,
   UnitCode             varchar(100)         null,
   UnitFullName         varchar(500)         null,
   DoorTypeIdentifier   varchar(100)         null,
   OrganIdentifier      varchar(100)         null,
   constraint PK_RMUNIT primary key (UnitIdentifier)
)";
                Message = SqlHelper.ExcuteQueryReturnMessage(SQL, CommandType.Text);
            }
            list.Add("创建[RmUnit]表:" + Message);
            return list;
        }

        public List<string> ExecuteSQL2013101001(bool IsExcute)
        {
            list = new List<string>();

            if (IsExcute)
            {
                string SQL = @"IF not exists (SELECT 1 from sysobjects where OBJECT_ID('CsValidateRoomPic')=id)
CREATE table CsValidateRoomPic
(
	RoomPicIdentifier  varchar(100) primary key ,
	BusninessIdentifier varchar(100),
	ImagePath varchar(200),
	ImageName varchar(100)
)";
                Message = SqlHelper.ExcuteQueryReturnMessage(SQL, CommandType.Text);
            }
            list.Add("创建[CsValidateRoomPic]表:" + Message);
            return list;
        }


        public List<string> ExecuteSQL2013101601(bool IsExcute)
        {
            list = new List<string>();

            if (IsExcute)
            {
                string SQL = @"if not exists (select * from syscolumns where id = object_id('RmUnit') and name='BuildingName')
alter table RmUnit
add BuildingName varchar(100)

if not exists (select * from syscolumns where id = object_id('RmUnit') and name='PavilionName')
alter table RmUnit
add PavilionName varchar(100)

if not exists (select * from syscolumns where id = object_id('RmUnit') and name='Floor')
alter table RmUnit
add [Floor] varchar(100)";
                Message = SqlHelper.ExcuteQueryReturnMessage(SQL, CommandType.Text);
            }
            list.Add("添加[BuildingName],[PavilionName],[Floor]字段到[RmUnit]表:" + Message);
            return list;
        }

        public List<string> ExecuteSQL2013102401(bool IsExcute)
        {
            list = new List<string>();

            if (IsExcute)
            {
                string SQL = @"IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'PcDict') AND type in (N'U'))
Create table PcDict (
   DictIdentifier       varchar(100)         not null  primary key,
   DictCode             varchar(100)         null,
   DictName             varchar(100)         null,
   DictTypeCode         varchar(100)         null 
)";
                Message = SqlHelper.ExcuteQueryReturnMessage(SQL, CommandType.Text);
            }
            list.Add("创建[PcDict]表:" + Message);
            return list;
        }


        public List<string> ExecuteSQL2013102501(bool IsExcute)
        {
            list = new List<string>();

            if (IsExcute)
            {
                string SQL = @"if Not exists (select 1   from  sysobjects   where  id = object_id('CsMaintanceProject')    and   type = 'U')
create table CsMaintanceProject (
   MainProIdentifier    varchar(100)         not null  primary key,
   SortIdentitfier      varchar(100)         null,
   SortName             varchar(100)         null,
   SortCode             varchar(100)         null,
   ProjectIdentitfier   varchar(100)         null,
   ProjectName          varchar(100)         null,
   ProjectCode          varchar(100)         null,
   ProOrganIdentifier      varchar(100)         null 
)";
                Message = SqlHelper.ExcuteQueryReturnMessage(SQL, CommandType.Text);
            }
            list.Add("创建[CsMaintanceProject]表:" + Message);
            return list;
        }

        public List<string> ExecuteSQL2013102601(bool IsExcute)
        {
            list = new List<string>();

            if (IsExcute)
            {
                string SQL = @"if Not exists (select 1  from  sysobjects where  id = object_id('CsMaintenanceDetail')  and   type = 'U')
create table CsMaintenanceDetail (
   MainDetailIdentifier varchar(100)         not null,
   MainIdentifier       varchar(100)         null,
   ProjectIdentitfier   varchar(100)         null,
   OrganIdentifier      varchar(100)         null,
   CreateDateTime       varchar(100)         null,
   constraint PK_CSMAINTENANCEDETAIL primary key (MainDetailIdentifier)
)";
                Message = SqlHelper.ExcuteQueryReturnMessage(SQL, CommandType.Text);
            }
            list.Add("创建[CsMaintenanceDetail]表:" + Message);
            return list;
        }

        public List<string> ExecuteSQL2013102602(bool IsExcute)
        {
            list = new List<string>();

            if (IsExcute)
            {
                string SQL = @"if not exists (select * from syscolumns where id = object_id('CsMaintenance') and name='UnitIdentifier')
alter table CsMaintenance
add UnitIdentifier varchar(100)

if not exists (select * from syscolumns where id = object_id('CsMaintenance') and name='OrganIdentifier')
alter table CsMaintenance
add OrganIdentifier varchar(100)";
                Message = SqlHelper.ExcuteQueryReturnMessage(SQL, CommandType.Text);
            }
            list.Add("在表[CsMaintenance]中添加[UnitIdentifier],[OrganIdentifier]字段:" + Message);
            return list;
        }


        public List<string> ExecuteSQL2013102901(bool IsExcute)
        {
            list = new List<string>();

            if (IsExcute)
            {
                string SQL = @"if Not exists (select 1   from  sysobjects where  id = object_id('CsMaintenanceFile')  and   type = 'U')
create table CsMaintenanceFile (
   CsMaintenanceFileIdentitfier 	varchar(100) primary key    not null,
   BusninessIdentitfier          varchar(100)     null,
   BusninessType        varchar(100)         null,
   ImgType              varchar(100)         null,
   ImgName              varchar(100)         null,
 ImagePath              varchar(100)         null,
   StaffName            varchar(100)         null
)";
                Message = SqlHelper.ExcuteQueryReturnMessage(SQL, CommandType.Text);
            }
            list.Add("创建[CsMaintenanceFile]表:" + Message);
            return list;
        }



    }
}
